﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlServerCe;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using System.Xml;
using ExportAllTable.Properties;

namespace ExportAllTable
{
    public partial class Form1 : Form
    {
        private List<string> tables;
        private SqlCeConnection connection;
        private Thread thread;
        private delegate void AddString(string str);
        private event AddString AddCompleateTable;

        public Form1()
        {
            InitializeComponent();
            connection = new SqlCeConnection("Data Source=E:\\work\\Repository\\MobileSales\\more\\ExportAllTable\\ExportAllTable\\bin\\Debug\\MobileSalesDB.sdf");
            tables = new List<string>();
        }

        public void AddListRow (string str)
        {
            listBox1.Items.Add(str);
        }

        public void LoadTable ()
        {
            AddCompleateTable += AddListRow;
            try
            {
                connection.Open();
            }
            catch(Exception ex)
            {
                new Exception("Error on connect");
            }
            try
            {
                string sqlQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    SqlCeDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        tables.Add(dr[0].ToString());
                    }
                }
            }
            finally
            {
                connection.Close();
            }
        }

        public void ExportData ()
        {
            DataSet exportTable = new DataSet();
            connection.Open();
            using (SqlCeCommand command = new SqlCeCommand("", connection))
            {
                DateTime startTime;
                DateTime endTime;
                command.CommandType = CommandType.TableDirect;
                foreach (string table in tables)
                {
                     startTime = DateTime.Now;
                    //exportTable = 
                    command.CommandText = table;
                    
                    new SqlCeDataAdapter(command).Fill(exportTable, table);
                    foreach (DataColumn c in exportTable.Tables[table].Columns)
                    {
                        c.ColumnMapping = MappingType.Attribute;
                    }
                    
                    exportTable.Tables[table].WriteXml(table+".xml");
                    endTime = DateTime.Now;
                    this.Invoke(AddCompleateTable, table + " - " + (endTime - startTime).ToString() + " CountRow: " + exportTable.Tables[table].Rows.Count);
                }
                exportTable.Clear();
                startTime = DateTime.Now;
                command.CommandType = CommandType.Text;
                command.CommandText = "Select IdGood, Remaining from Goods order by IdGood";
                new SqlCeDataAdapter(command).Fill(exportTable, "Goods");
                foreach (DataColumn c in exportTable.Tables["Goods"].Columns)
                {
                    c.ColumnMapping = MappingType.Attribute;
                }
                exportTable.Tables["Goods"].WriteXml("Remaining" + ".xml");
                endTime = DateTime.Now;
                this.Invoke(AddCompleateTable, "Remaining" + " - " + (endTime - startTime).ToString() + " CountRow: " + exportTable.Tables["Goods"].Rows.Count);

                
                startTime = DateTime.Now;
                exportTable = new DataSet();
                command.CommandText = "SELECT IdBuyPoint, Debts FROM BuyPoints order by IdBuyPoint";
                command.ExecuteReader();
                SqlCeDataAdapter da = new SqlCeDataAdapter(command);
                da.Fill(exportTable, "BuyPoints");
                foreach (DataColumn c in exportTable.Tables["BuyPoints"].Columns)
                {
                    c.ColumnMapping = MappingType.Attribute;
                }
                exportTable.Tables["BuyPoints"].WriteXml("Debts" + ".xml");
                endTime = DateTime.Now;
                this.Invoke(AddCompleateTable, "Debts" + " - " + (endTime - startTime).ToString() + " CountRow: " + exportTable.Tables["BuyPoints"].Rows.Count);

            }
            connection.Close();
            MessageBox.Show(Resources.Form1_ExportData_OK___);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            LoadTable();
            thread = new Thread(new ThreadStart(ExportData)){IsBackground = true};
            thread.Start();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (thread != null)
            {
                thread.Abort();
            }
        }
    }
}
